package com.api.gbhg.huangwm.dao;

import com.alibaba.fastjson.JSON;
import com.customization.commons.LogTool;
import weaver.conn.RecordSet;
import weaver.conn.RecordSetTrans;
import weaver.general.BaseBean;

import java.sql.SQLException;
import java.util.*;

/**
 * @Author Huangwm
 * @Date 2022/04/19
 * @Version 1.0
 * @Description TODO 报销撤单功能-退回所扣减的uf_jingfei,uf_jingfeilaiyuan,以及所属收入预算的金额
 *              退还金额
 *              1、先查询报销流程的数据
 *              2、退还uf_jingfei表的部门经济分类额度
 *              3、根据上年和当年退还uf_jingfeilaiyuan表的核批渠道额度
 *              4、查询预算项目所对应的独立核算单位和预算年度、根据上年和当年退回报销所扣除支出预算来源的金额（财政拨款和非财政拨款）
 *              5、删除uf_bxsjjl记录
 */
public class ReturnReimbursementDao extends BaseBean{
    private LogTool logTool = new LogTool("/log/gbhg/huangwm/dao/ReturnReimbursementDao",false);

    public static List<String> wfids_fullDetail = new ArrayList<>();//纯明细报销流程
    public static List<String> wfids_fullMain = new ArrayList<>();//纯主表报销流程
    public static List<String> wfids_detailAndMain = new ArrayList<>();//费用信息主表支付信息明细
    public static List<String> wfids_ry = new ArrayList<>();//人员经费报销
    public static String jiekuan = "";//BX27
    public static String huankuan = "";//BX28
    public static String zhibaojinjiaona = "";//BX17
    public static String zhibaojintuihuan = "";//BX18

    public List<String> table_fullDetail = Arrays.asList(
            "formtable_main_892","formtable_main_894","formtable_main_895","formtable_main_897","formtable_main_899",
            "formtable_main_901","formtable_main_902","formtable_main_903","formtable_main_904","formtable_main_919",
            "formtable_main_905"
    );//纯明细报销流程表名 费用信息-报销金额为sqbxje
    public  List<String> table_fullMain =  Arrays.asList(
            "formtable_main_900","formtable_main_911"
    );//纯主表报销流程 费用信息-报销金额为sqbxje
    public  List<String> table_detailAndMain = Arrays.asList(
            "formtable_main_896"
    );//费用信息主表支付信息明细 费用信息-报销金额为sqbxje
    public  List<String> table_ry = Arrays.asList(
            "formtable_main_913","formtable_main_914","formtable_main_915","formtable_main_916","formtable_main_917"
    );//人员经费报销 费用信息-报销金额为sqbxje

    static {
        BaseBean baseBean = new BaseBean();
        String YS02Workflowid = baseBean.getPropValue("ReimbursementWorkflowid","YS02Workflowid");
        String YS04Workflowid = baseBean.getPropValue("ReimbursementWorkflowid","YS04Workflowid");
        String YS05Workflowid = baseBean.getPropValue("ReimbursementWorkflowid","YS05Workflowid");
        String YS06Workflowid = baseBean.getPropValue("ReimbursementWorkflowid","YS06Workflowid");
        String YS07Workflowid = baseBean.getPropValue("ReimbursementWorkflowid","YS07Workflowid");
        String YS09Workflowid = baseBean.getPropValue("ReimbursementWorkflowid","YS09Workflowid");
        String YS10Workflowid = baseBean.getPropValue("ReimbursementWorkflowid","YS10Workflowid");
        String YS11Workflowid = baseBean.getPropValue("ReimbursementWorkflowid","YS11Workflowid");
        String YS12Workflowid = baseBean.getPropValue("ReimbursementWorkflowid","YS12Workflowid");
        String YS13Workflowid = baseBean.getPropValue("ReimbursementWorkflowid","YS13Workflowid");
        String YS14Workflowid = baseBean.getPropValue("ReimbursementWorkflowid","YS14Workflowid");
        String YS15Workflowid = baseBean.getPropValue("ReimbursementWorkflowid","YS15Workflowid");
        String YS16Workflowid = baseBean.getPropValue("ReimbursementWorkflowid","YS16Workflowid");
        wfids_fullDetail.add(YS02Workflowid);
        wfids_fullDetail.add(YS04Workflowid);
        wfids_fullDetail.add(YS05Workflowid);
        wfids_fullDetail.add(YS07Workflowid);
        wfids_fullDetail.add(YS09Workflowid);
        wfids_fullDetail.add(YS11Workflowid);
        wfids_fullDetail.add(YS12Workflowid);
        wfids_fullDetail.add(YS13Workflowid);
        wfids_fullDetail.add(YS14Workflowid);
        wfids_fullDetail.add(YS15Workflowid);
        wfids_fullDetail.add(YS16Workflowid);

        wfids_detailAndMain.add(YS06Workflowid);

        wfids_fullMain.add(YS10Workflowid);

        String YS17Workflowid = baseBean.getPropValue("ReimbursementWorkflowid","YS17Workflowid");
        zhibaojinjiaona = YS17Workflowid;
        String YS18Workflowid= baseBean.getPropValue("ReimbursementWorkflowid","YS18Workflowid");
        zhibaojintuihuan = YS18Workflowid;
        String YS27Workflowid = baseBean.getPropValue("ReimbursementWorkflowid","YS27Workflowid");
        jiekuan = YS27Workflowid;
        String YS28Workflowid = baseBean.getPropValue("ReimbursementWorkflowid","YS28Workflowid");
        huankuan = YS28Workflowid;

        String YS29Workflowid = baseBean.getPropValue("ReimbursementWorkflowid","YS29Workflowid");
        String YS30Workflowid = baseBean.getPropValue("ReimbursementWorkflowid","YS30Workflowid");
        String YS31Workflowid = baseBean.getPropValue("ReimbursementWorkflowid","YS31Workflowid");
        String YS32Workflowid = baseBean.getPropValue("ReimbursementWorkflowid","YS32Workflowid");
        String YS33Workflowid = baseBean.getPropValue("ReimbursementWorkflowid","YS33Workflowid");
        wfids_ry.add(YS29Workflowid);
        wfids_ry.add(YS30Workflowid);
        wfids_ry.add(YS31Workflowid);
        wfids_ry.add(YS32Workflowid);
        wfids_ry.add(YS33Workflowid);
    }

    /**
     * 获取流程数据
     * @param requrstInfo
     * @return
     * @throws Exception
     */
    public List<Map<String,String>> getRequestData(Map<String,String> requrstInfo) throws Exception{
        logTool.writeLog("查询流程数据 getRequestData");
        long time = System.currentTimeMillis();
        String requestid = requrstInfo.get("requestid");
        String table = requrstInfo.get("tablename");
        String fullTable = "";
        String sql = "";
        if(table_fullDetail.contains(table)){
            fullTable = table+"_dt1";
            sql = "select nd,tybxje,sqbxje,ysxm,bmjjzcfl,zfjjzcfl,zcqd,ysly,xzkmdm,fzysrfl,zcdfczbkxm from "+fullTable+" where mainid=(select id from "+table+" where requestid="+requestid+")";
        }else if(table_detailAndMain.contains(table) || table_fullMain.contains(table)){
            fullTable = table;
            sql = "select nd,tybxje,sqbxje,ysxm,bmjjzcfl,zfjjzcfl,zcqd,ysly,xzkmdm,fzysrfl,zcdfczbkxm from "+fullTable+" where  requestid="+requestid;
        }else if(table_ry.contains(table)){
            fullTable = table+"_dt1";
            sql = "select nd,tybxje,sqbxje,ysxm,bmjjzcfl,zfjjzcfl,zcqd,ysly,xzkmdm,fzysrfl,zcdfczbkxm from "+fullTable+" where mainid=(select id from "+table+" where requestid="+requestid+")";
        }else{
            throw new Exception("requestid["+requestid+"]为非报销流程!");
        }

        List<Map<String,String>> lists = new ArrayList<>();
        //RecordSetTrans rs = new RecordSetTrans();
        RecordSet rs = new RecordSet();
        if(!rs.execute(sql)){
            throw new SQLException("查询报销流程数据出错>"+sql);
        }
        while (rs.next()){
            Map<String,String> data = new HashMap<>();
            data.put("nd",rs.getString("nd"));
            data.put("ysxm",rs.getString("ysxm"));
            data.put("bmjjzcfl",rs.getString("bmjjzcfl"));
            data.put("zfjjzcfl",rs.getString("zfjjzcfl"));
            data.put("zcqd",rs.getString("zcqd"));
            data.put("ysly",rs.getString("ysly"));
            data.put("xzkmdm",rs.getString("xzkmdm"));
            data.put("hpssjid",rs.getString("xzkmdm").isEmpty()?"":getCzbkid(rs.getString("xzkmdm")));
            data.put("fzysrfl",rs.getString("fzysrfl"));
            data.put("zcdfczbkxm",rs.getString("zcdfczbkxm"));
            data.put("sqbxje",rs.getString("sqbxje"));
            data.put("tybxje",rs.getString("tybxje"));
            data.put("chae",(rs.getDouble("sqbxje")-rs.getDouble("tybxje"))+"");
            lists.add(data);
        }
        time = System.currentTimeMillis() - time;
        logTool.writeLog("查询流程数据总耗时(秒)：" + (time / 1000));
        logTool.writeLog(JSON.toJSONString(lists));
        return lists;
    }

    /**
     * 部门预算经费额度释放
     * @param requestData
     * @param isreturn true为释放 false为扣减用于执行其他程序异常时收回执行
     * @return
     */
    public boolean uf_jingfei(List<Map<String,String>> requestData,boolean isreturn) throws Exception{
        if(requestData.size()<=0){
            return false;
        }
        logTool.writeLog("部门预算经费额度释放 uf_jingfei");
        List<List<Object>> params = new ArrayList<>();
        String returnSql = "update uf_jingfei set syed=syed"+(isreturn?"+":"-")+"?,ybx=ybx"+(isreturn?"-":"+")+"? where lcjlid=? and bmzcjjfl=? ";
        for (Map<String,String> data : requestData){
            List<Object> pdata = new ArrayList<>();
            String ysxm = data.get("ysxm");
            String bmjjzcfl = data.get("bmjjzcfl");
            String tybxje = data.get("tybxje");
            //原本冻结1000，同意报销900，最后释放100，实际按照同意报销金额扣减
            pdata.add(tybxje);
            pdata.add(tybxje);
            pdata.add(ysxm);
            pdata.add(bmjjzcfl);
            params.add(pdata);
        }
        boolean result = true;
        RecordSetTrans rst = new RecordSetTrans();
        rst.setAutoCommit(false);
        try {
            rst.executeBatchSql(returnSql,params);
            logTool.writeLog("释放uf_jingfei参数");
            logTool.writeLog(JSON.toJSONString(params));
        }catch (Exception e){
            result = false;
            rst.rollback();
            logTool.writeLog("释放uf_jingfei失败;"+e.getMessage());
            throw new SQLException("释放uf_jingfei失败"+e.getMessage());
        }
        if(result) rst.commit();
        return result;
    }

    /**
     * 预算经费来源额度释放
     * @param requestData
     * @param isreturn true为释放 false为扣减用于执行其他程序异常时收回执行
     * @return
     * @throws Exception
     */
    public boolean uf_jingfeilaiyuan(List<Map<String,String>> requestData,boolean isreturn) throws Exception{
        if(requestData.size()<=0){
            return false;
        }
        logTool.writeLog("预算经费来源额度释放 uf_jingfeilaiyuan");
        //财政拨款
        List<List<Object>> params_zycz = new ArrayList<>();
        String returnSql_zycz = "update uf_jingfeilaiyuan set syed=syed"+(isreturn?"+":"-")+"? where lcid=? and srlx=0 and zyczbksr=? and yslylx=?";
        //非中央财政
        List<List<Object>> params_fzy = new ArrayList<>();
        String returnSql_fzycz = "update uf_jingfeilaiyuan set syed=syed"+(isreturn?"+":"-")+"? where lcid=? and srlx=1 and zcqd=? and yslylx=?";
        //非中央财政-项目型
        List<List<Object>> params_fzyxm = new ArrayList<>();
        String returnSql_fzyczxm = "update uf_jingfeilaiyuan set syed=syed"+(isreturn?"+":"-")+"? where lcid=? and srlx=1 and zcqd=?  and dfczbkxm=? and yslylx=?";
        for (Map<String,String> data : requestData){
            List<Object> pdata_cz = new ArrayList<>();
            List<Object> pdata_fzy = new ArrayList<>();
            List<Object> pdata_fzyxm = new ArrayList<>();
            String nd = data.get("nd");
            String ysxm = data.get("ysxm");
            String fzysrfl = data.get("fzysrfl");//非中央收入来源
            String zcdfczbkxm = data.get("zcdfczbkxm");//非中央收入来源为5和8时有值
            String tybxje = data.get("tybxje");
            //支出渠道 0-财政拨款 1-非财政拨款 对应经费来源表的srlx的0和1
            String zcqd = data.get("zcqd");
            //预算来源 0-当年预算 1-上年结转 对应经费来源表中的yslylx的当年预算核批和上年结转核批
            String ysly = "0".equals(data.get("ysly"))?"当年预算核批":"上年结转核批";
            String hpssjid = data.get("hpssjid");
            if("0".equals(zcqd)){
                pdata_cz.add(tybxje);
                pdata_cz.add(ysxm);
                pdata_cz.add(hpssjid);
                pdata_cz.add(ysly);
                params_zycz.add(pdata_cz);
            }else{
                if("5".equals(fzysrfl) || "8".equals(fzysrfl)){
                    pdata_fzyxm.add(tybxje);
                    pdata_fzyxm.add(ysxm);
                    pdata_fzyxm.add(fzysrfl);
                    pdata_fzyxm.add(zcdfczbkxm);
                    pdata_fzyxm.add(ysly);
                    params_fzyxm.add(pdata_fzyxm);
                }else{
                    pdata_fzy.add(tybxje);
                    pdata_fzy.add(ysxm);
                    pdata_fzy.add(fzysrfl);
                    pdata_fzy.add(ysly);
                    params_fzy.add(pdata_fzy);
                }
            }
        }
        boolean result_cz = true,result_fzy = true,result_fzyxm = true;
        RecordSetTrans rst = new RecordSetTrans();
        rst.setAutoCommit(false);
        try {
            if(params_zycz.size()>0){
                rst.executeBatchSql(returnSql_zycz,params_zycz);
                logTool.writeLog("中央财政释放参数");
                logTool.writeLog(JSON.toJSONString(params_zycz));
            }
        }catch (Exception e){
            result_cz = false;
            rst.rollback();
            logTool.writeLog("释放uf_jingfeilaiyuan中的财政拨款失败;"+e.getMessage());
            throw new SQLException("释放uf_jingfeilaiyuan中的财政拨款失败"+e.getMessage());
        }
        try {
            if(params_fzy.size()>0){
                rst.executeBatchSql(returnSql_fzycz,params_fzy);
                logTool.writeLog("非中央财政释放参数");
                logTool.writeLog(JSON.toJSONString(params_fzy));
            }
        }catch (Exception e){
            result_fzy = false;
            rst.rollback();
            logTool.writeLog("释放uf_jingfeilaiyuan中的非中央财政失败;"+e.getMessage());
            throw new SQLException("释放uf_jingfeilaiyuan中的非中央财政失败"+e.getMessage());
        }
        try {
            if(params_fzyxm.size()>0) {
                rst.executeBatchSql(returnSql_fzyczxm, params_fzyxm);
                logTool.writeLog("非中央财政项目释放参数");
                logTool.writeLog(JSON.toJSONString(params_fzyxm));
            }
        }catch (Exception e){
            result_fzyxm = false;
            rst.rollback();
            logTool.writeLog("释放uf_jingfeilaiyuan中的非中央财政项目失败;"+e.getMessage());
            throw new SQLException("释放uf_jingfeilaiyuan中的非中央财政项目失败"+e.getMessage());
        }
        //全部成功
        if(result_cz && result_fzy && result_fzyxm){
            rst.commit();
            return true;
        }else{
            rst.rollback();
            return  false;
        }
    }

    /**
     * 收入预算额度释放
     * @param requestData
     * @param isreturn true为释放 false为扣减用于执行其他程序异常时收回执行
     * @return
     * @throws Exception
     */
    public boolean shouruyusuan(List<Map<String,String>> requestData,boolean isreturn) throws Exception{
        if(requestData.size()<=0){
            return false;
        }
        logTool.writeLog("收入预算释放(财政与非财政收入) shouruyusuan");
        //int yj = Integer.parseInt(getPropValue("Verification","yj"));//科目级
        //int xj = Integer.parseInt(getPropValue("Verification","xj"));//下级
        int yj = 27;//科目级
        int xj = 28;//下级
        //财政拨款科目表
        List<List<Object>> params_zyczkm = new ArrayList<>();
        String returnSql_zyczkm = "update uf_zyczbksr_km set hpsyje=hpsyje"+(isreturn?"+":"-")+"?,sjbxje=sjbxje"+(isreturn?"-":"+")+"?,snjzhpsyje=snjzhpsyje"+(isreturn?"+":"-")+"?,snjzhpbxje=snjzhpbxje"+(isreturn?"-":"+")+"? where id=?";
        //财政拨款科目下级表
        List<List<Object>> params_zyczkmxj = new ArrayList<>();
        String returnSql_zyczkmxj = "update uf_zyczbksr_xj set yhpsy=yhpsy"+(isreturn?"+":"-")+"?,sjbxje=sjbxje"+(isreturn?"-":"+")+"?,snjzhpsyje=snjzhpsyje"+(isreturn?"+":"-")+"?,snjzhpbxje=snjzhpbxje"+(isreturn?"-":"+")+"? where id=?";
        //非中央财政
        List<List<Object>> params_fzy = new ArrayList<>();
        List<List<Object>> params_fzy_sup = new ArrayList<>();//专为收入分类为5和8时需要同步扣减所在分类级使用 实际收入剩余扣减的需要增加回去
        String returnSql_fzycz = "update uf_fzysr set hpsyje=hpsyje"+(isreturn?"+":"-")+"?,sjbxje=sjbxje"+(isreturn?"-":"+")+"?,snjzhpsyje=snjzhpsyje"+(isreturn?"+":"-")+"?,snjzhpbxje=snjzhpbxje"+(isreturn?"-":"+")+"?,sjsrsy=sjsrsy"+(isreturn?"+":"-")+"? where id=?";
        //非中央财政-项目型
        List<List<Object>> params_fzyxm = new ArrayList<>();
        String returnSql_fzyczxm = "update uf_fzysrxm set hpsyje=hpsyje"+(isreturn?"+":"-")+"?,sjbxje=sjbxje"+(isreturn?"-":"+")+"?,snjzhpsyje=snjzhpsyje"+(isreturn?"+":"-")+"?,snjzhpbxje=snjzhpbxje"+(isreturn?"-":"+")+"?,sjsrsy=sjsrsy"+(isreturn?"+":"-")+"? where id=?";
        for (Map<String,String> data : requestData){
            List<Object> pdata_czkm = new ArrayList<>();
            List<Object> pdata_czxj = new ArrayList<>();
            List<Object> pdata_fzy = new ArrayList<>();
            List<Object> pdata_fzyxm = new ArrayList<>();
            List<Object> pdata_fzy_sup = new ArrayList<>();
            String nd = data.get("nd");
            String ysxm = data.get("ysxm");
            String fzysrfl = data.get("fzysrfl");//非中央收入来源
            String zcdfczbkxm = data.get("zcdfczbkxm");//非中央收入来源为5和8时有值
            String tybxje = data.get("tybxje");
            //支出渠道 0-财政拨款 1-非财政拨款 对应经费来源表的srlx的0和1
            String zcqd = data.get("zcqd");
            //预算来源 0-当年预算 1-上年结转 对应经费来源表中的yslylx的当年预算核批和上年结转核批
            String ysly = data.get("ysly");
            String hpssjid = data.get("hpssjid");
            if("0".equals(zcqd)){
                String[] sta = hpssjid.split("_");//财政拨款核批数据分割
                int sum1 = Integer.parseInt(sta[0]);//27为科目级 28为科目下级
                int sum2 = Integer.parseInt(sta[1]);//表id
                if(sum1 == yj){
                    //财政拨款时判断上年还是当年
                    if("0".equals(ysly)){
                        pdata_czkm.add(tybxje);
                        pdata_czkm.add(tybxje);
                        pdata_czkm.add(0);
                        pdata_czkm.add(0);
                        pdata_czkm.add(sum2);
                    }else{
                        pdata_czkm.add(0);
                        pdata_czkm.add(0);
                        pdata_czkm.add(tybxje);
                        pdata_czkm.add(tybxje);
                        pdata_czkm.add(sum2);
                    }
                    params_zyczkm.add(pdata_czkm);
                }else{
                    //财政拨款下级时判断上年还是当年
                    if("0".equals(ysly)){
                        pdata_czxj.add(tybxje);
                        pdata_czxj.add(tybxje);
                        pdata_czxj.add(0);
                        pdata_czxj.add(0);
                        pdata_czxj.add(sum2);
                    }else{
                        pdata_czxj.add(0);
                        pdata_czxj.add(0);
                        pdata_czxj.add(tybxje);
                        pdata_czxj.add(tybxje);
                        pdata_czxj.add(sum2);
                    }
                    params_zyczkmxj.add(pdata_czxj);
                }
            }else{
                //非财政项目支出或其他收入支出zcdfczbkxm为具体的表id
                if("5".equals(fzysrfl) || "8".equals(fzysrfl)){
                    String fzrsrId = getFzysrId(zcdfczbkxm);
                    if("0".equals(ysly)){
                        pdata_fzyxm.add(tybxje);
                        pdata_fzyxm.add(tybxje);
                        pdata_fzyxm.add(0);
                        pdata_fzyxm.add(0);
                        pdata_fzyxm.add(tybxje);
                        pdata_fzyxm.add(zcdfczbkxm);
                        //再扣上级
                        pdata_fzy_sup.add(tybxje);
                        pdata_fzy_sup.add(tybxje);
                        pdata_fzy_sup.add(0);
                        pdata_fzy_sup.add(0);
                        pdata_fzy_sup.add(tybxje);
                        pdata_fzy_sup.add(fzrsrId);
                    }else{
                        pdata_fzyxm.add(0);
                        pdata_fzyxm.add(0);
                        pdata_fzyxm.add(tybxje);
                        pdata_fzyxm.add(tybxje);
                        pdata_fzyxm.add(0);//上年结转时没有实际收入
                        pdata_fzyxm.add(zcdfczbkxm);
                        //再扣上级
                        pdata_fzy_sup.add(0);
                        pdata_fzy_sup.add(0);
                        pdata_fzy_sup.add(tybxje);
                        pdata_fzy_sup.add(tybxje);
                        pdata_fzy_sup.add(0);//上年结转时没有实际收入
                        pdata_fzy_sup.add(fzrsrId);
                    }
                    params_fzyxm.add(pdata_fzyxm);
                    params_fzy_sup.add(pdata_fzy_sup);
                }else{
                    //非财政收入支出
                    String fzysrId = getFzysrId(ysxm,fzysrfl,nd);
                    if("0".equals(ysly)){
                        pdata_fzy.add(tybxje);
                        pdata_fzy.add(tybxje);
                        pdata_fzy.add(0);
                        pdata_fzy.add(0);
                        pdata_fzy.add(tybxje);
                        pdata_fzy.add(fzysrId);
                    }else{
                        pdata_fzy.add(0);
                        pdata_fzy.add(0);
                        pdata_fzy.add(tybxje);
                        pdata_fzy.add(tybxje);
                        pdata_fzy.add(0);//上年结转时没有实际收入
                        pdata_fzy.add(fzysrId);
                    }
                    params_fzy.add(pdata_fzy);
                }
            }
        }
        boolean result_czkm = true,result_czxj = true,result_fzy = true,result_fzyxm = true,result_fzy_sup = true;
        RecordSetTrans rst = new RecordSetTrans();
        rst.setAutoCommit(false);
        try {
            if(params_zyczkm.size()>0){
                rst.executeBatchSql(returnSql_zyczkm,params_zyczkm);
                logTool.writeLog("中央财政科目表释放参数");
                logTool.writeLog(JSON.toJSONString(params_zyczkm));
            }
        }catch (Exception e){
            result_czkm = false;
            rst.rollback();
            logTool.writeLog("释放中央财政科目表报销金额失败"+e.getMessage());
            throw new SQLException("释放中央财政科目表报销金额失败"+e.getMessage());
        }
        try {
            if(params_zyczkmxj.size()>0){
                rst.executeBatchSql(returnSql_zyczkmxj,params_zyczkmxj);
                logTool.writeLog("中央财政科目下级表释放参数");
                logTool.writeLog(JSON.toJSONString(params_zyczkmxj));
            }
        }catch (Exception e){
            result_czxj = false;
            rst.rollback();
            logTool.writeLog("释放中央财政科目下级表报销金额失败"+e.getMessage());
            throw new SQLException("释放中央财政科目下级表报销金额失败"+e.getMessage());
        }
        try {
            if(params_fzy.size()>0){
                rst.executeBatchSql(returnSql_fzycz,params_fzy);
                logTool.writeLog("非中央财政收入表释放参数");
                logTool.writeLog(JSON.toJSONString(params_fzy));
            }
        }catch (Exception e){
            result_fzy = false;
            rst.rollback();
            logTool.writeLog("释放非中央财政收入表报销金额失败"+e.getMessage());
            throw new SQLException("释放非中央财政收入表报销金额失败"+e.getMessage());
        }
        try {
            if(params_fzyxm.size()>0){
                rst.executeBatchSql(returnSql_fzyczxm,params_fzyxm);
                logTool.writeLog("非中央财政收入项目表释放参数");
                logTool.writeLog(JSON.toJSONString(params_fzyxm));
            }
        }catch (Exception e){
            result_fzyxm = false;
            rst.rollback();
            logTool.writeLog("释放非中央财政收入项目表报销金额失败"+e.getMessage());
            throw new SQLException("释放非中央财政收入项目表报销金额失败"+e.getMessage());
        }
        try {
            if(params_fzy_sup.size()>0){
                rst.executeBatchSql(returnSql_fzycz,params_fzy_sup);
                logTool.writeLog("非中央财政收入表释放参数(项目逐级扣)");
                logTool.writeLog(JSON.toJSONString(params_fzy_sup));
            }
        }catch (Exception e){
            result_fzy_sup = false;
            rst.rollback();
            logTool.writeLog("释放非中央财政收入表报销金额失败(项目逐级扣)"+e.getMessage());
            throw new SQLException("释放非中央财政收入表报销金额失败(项目逐级扣)"+e.getMessage());
        }
        if(result_czkm && result_czxj && result_fzy && result_fzyxm && result_fzy_sup){
            rst.commit();
            return true;
        }else{
            rst.rollback();
            return  false;
        }
    }

    /**
     * 撤单成功后更新记录表中的节点id和节点类型
     * @param lcid
     */
    public void updateRecord(String lcid,String cnodeid){
        new RecordSet().execute("update uf_bxjl_kj set dqjdid="+cnodeid+",dqjdlx=0 where lcid="+lcid);
    }
    /**
     * 删除基本报销记录
     * @param requestid
     * @return
     */
    public void deleteRecords(String requestid){
        RecordSet rs = new RecordSet();
        rs.execute("delete from uf_bxsjjl where lcid="+requestid);
        rs.execute("delete from uf_jingfei_dt1 where bxlcjlid="+requestid);//非人员经费报销记录
        rs.execute("delete from uf_jingfei_dt2 where bxlcid="+requestid);//人员经费报销记录
        //删除报销记录
        rs.execute("DELETE FROM uf_bxjl_kj WHERE lcid="+requestid);
        rs.execute("DELETE FROM uf_bxjl_kj_dt1 WHERE lcid="+requestid);
        rs.execute("DELETE FROM uf_bxjl_kj_dt2 WHERE lcid="+requestid);
    }

    /**
     * 根据requestid获取关联信息
      * @param requestid
     * @return
     */
    public Map<String,String> getRequrstInfo(String requestid){
        if(requestid.isEmpty()) return null;
        logTool.writeLog("根据requestid获取关联信息");
        Map<String,String> result = new HashMap<>();
        result.put("requestid",requestid);
        RecordSet rs = new RecordSet();
        String sql =
                "select wr.workflowid,tablename,creater,currentnodetype,wfn.nodeid as firstnodeid \n" +
                ",wr.requestname,wr.requestmark \n"+
                "from workflow_requestbase wr left join workflow_base wb on wr.workflowid=wb.id \n" +
                "left join workflow_bill bill on bill.id = wb.formid\n" +
                "left join workflow_flownode wfn  on wfn.workflowid = wr.workflowid\n" +
                "where nodetype=0 and requestid="+requestid;
        rs.execute(sql);
        logTool.writeLog("getWorkflowidAndTableName--->"+sql);
        if(rs.next()){
            result.put("workflowid",rs.getString("workflowid"));
            result.put("tablename",rs.getString("tablename"));
            result.put("creater",rs.getString("creater"));
            result.put("currentnodetype",rs.getString("currentnodetype"));
            result.put("firstnodeid",rs.getString("firstnodeid"));
            result.put("requestname",rs.getString("requestname"));
            result.put("requestmark",rs.getString("requestmark"));
        }else{
            return  null;
        }
        logTool.writeLog(JSON.toJSONString(result));
        return result;
    }

    /**
     * 根据费用信息中的xzkmdm字段获取对应的财政拨款核批数据id
     * @param xzkmdm
     * @return 27_id or 28_id(27科目级 28科目下级)
     */
    public String getCzbkid(String xzkmdm){
        if(xzkmdm.isEmpty()) return "";
        RecordSet rs = new RecordSet();
        rs.execute("select * from uf_gxmdygnkm where id="+xzkmdm);
        if (rs.next()){
            return rs.getString("hpssjid");
        }
        return "";
    }

    /**
     * 根据预算项目，年份和收入类型查询非财政收入
     * @param ysxm
     * @param szfl
     * @param nf
     * @return
     */
    public String getFzysrId(String ysxm,String szfl,String nf) throws Exception{
        if(ysxm.isEmpty() || szfl.isEmpty() || nf.isEmpty()) return "0";
        RecordSet rs = new RecordSet();
        String sql = "select * from uf_fzysr where  szfl="+szfl+" and nf="+nf+" and dw=(select dlhsdw from uf_jingfei_version where lcjlsqjlid="+ysxm+")";
        if(!rs.execute(sql)){
            throw new SQLException("查询非财政收入出错,sql>"+sql);
        }
        if (rs.next()){
            return rs.getString("id");
        }
        return "0";
    }
    /**
     * 根据地方财政拨款项目id获取对应非中央收入表id
     * @param dfczbkxm
     * @return
     */
    public String getFzysrId(String dfczbkxm){
        if(dfczbkxm.isEmpty()) return "0";
        RecordSet rs = new RecordSet();
        rs.execute("select * from uf_fzysrxm where id="+dfczbkxm);
        if (rs.next()){
            return rs.getString("szfl");
        }
        return "";
    }

    /**
     * 判断用户是否在报销流程的会计节点操作者内
     * @param requestid
     * @param userid
     * @return
     * @throws Exception
     */
    public boolean isOperatorKj(String requestid,int userid) throws Exception{
        if(requestid.isEmpty()) throw  new Exception("requestid为空");
        String sql = "select * from workflow_currentoperator where requestid="+requestid+" and  nodeid in(\n" +
                "select nodeid from workflow_flownode wfn left join workflow_nodebase wn on wfn.nodeid=wn.id \n" +
                "where  (wn.nodename like'%会计%') \n" +
                "and workflowid=( select workflowid from workflow_requestbase where requestid="+requestid+")) \n"+
                "and userid="+userid;
        RecordSet rs = new RecordSet();
        if(rs.execute(sql) && rs.next()){
            return true;
        }
        return false;
    }
}
